package com.zk.atm.utils;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Map.Entry;

import oracle.sql.CLOB;

import org.apache.log4j.Logger;

public class AccessDBConnection {
	public static final Logger logger=Logger.getLogger(AccessDBConnection.class);
	public static String sys_db = "";
	private static String driver = "";
	private static String url = "";
	private static String user = "";
	private static String pwd = "";
	private static Statement stmt;
	private static ResultSet rs;

	static {
		Properties prop = new Properties();
		InputStream in = AccessDBConnection.class.getResourceAsStream("/dbconfig.properties");
		//System.out.println("****************in="+new File("dbconfig.properties").getAbsolutePath());
		try {
			prop.load(in);
		} catch (IOException e) {
			e.printStackTrace();
		}
//		sys_db = prop.getProperty("JDBC.").toLowerCase();
//		driver = prop.getProperty(sys_db + "driverClassName").trim();
//		url = prop.getProperty(sys_db + "url").trim();
//		user = prop.getProperty(sys_db + "username").trim();
//		pwd = prop.getProperty(sys_db + "password").trim();
		driver = prop.getProperty("JDBC.Driver").trim();
		url = prop.getProperty("JDBC.ConnectionURL").trim();
		user = prop.getProperty("JDBC.Username").trim();
		pwd = prop.getProperty("JDBC.Password").trim();
	}

	private static void initDriverManager() {
		//------Class.forName----
		if (sys_db.equals("mysql")) {
			try {
				Class.forName(driver).newInstance();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else {
			try {
				Class.forName(driver);
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
		}
	}

	public static Connection getConnection() {
		initDriverManager();
		Connection conn = null;
		try {
			if(conn==null||conn.isClosed()){
			  conn = DriverManager.getConnection(url, user, pwd);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	public static void closeConnection(Connection conn) throws Exception{
		try {
			if(conn!=null&&!conn.isClosed()){
				conn.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void insertClob() {
		Connection con = AccessDBConnection.getConnection();
		String sql = "insert into TEST_CLOB (id, title, content, author) values(2,'标题2',EMPTY_CLOB(),'张之洞')";

		try {
			con.setAutoCommit(false);
			stmt = con.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		Statement stmt2 = null;
		String selsql = "select content from TEST_CLOB where id = 1 for update ";
		try {
			stmt2 = con.createStatement();
			ResultSet rs = stmt2.executeQuery(selsql);
			while (rs.next()) {
				CLOB content = (CLOB) rs.getClob("content");
				BufferedWriter outer = new BufferedWriter(content
						.getCharacterOutputStream());
				String cont = "this is test content1";
				outer.write(cont, 0, cont.length());
				outer.close();
			}
			con.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				stmt2.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	public static void updateClob() {
		Connection con = AccessDBConnection.getConnection();
		Statement stmt2 = null;
		try {
			con.setAutoCommit(false);
			stmt = con.createStatement();
			String sql = "update TEST_CLOB set title = 'title2', author='author2' where id = 1";
			stmt.executeUpdate(sql);
			stmt2 = con.createStatement();
			String sql2 = "select title, content from TEST_CLOB where id = 1 for update";
			ResultSet rs = stmt2.executeQuery(sql2);
			while (rs.next()) {
				CLOB content = (CLOB) rs.getClob("content");
				BufferedWriter in = new BufferedWriter(content
						.getCharacterOutputStream());
				try {
					in.write("this content has been updated!");
					in.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			rs.close();
			con.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				stmt2.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public static void deleteClob() {
		Connection con = AccessDBConnection.getConnection();
		try {
			stmt = con.createStatement();
			String sql = "delete TEST_CLOB where id = 2";
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	public static List selectClob() {
		List row = new ArrayList();
		Connection con = AccessDBConnection.getConnection();
		ResultSet rs = null;
		try {
			stmt = con.createStatement();
			String sql = "select id, title, author, content from TEST_CLOB where id = 1";
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				row.add(rs.getInt("id"));
				row.add(rs.getString("title"));
				row.add(rs.getString("author"));
				CLOB content = (CLOB) rs.getClob("content");
				BufferedReader in = new BufferedReader(content
						.getCharacterStream());
				StringWriter out = new StringWriter();
				int c;
				try {
					while ((c = in.read()) != -1) {
						out.write(c);
					}
					in.close();
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
				row.add(out.toString());
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				stmt.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return row;
	}

	public static void main(String[] args) {
		//AccessDBH.deleteClob();
		//   List row = AccessDBH.selectClob();
		//   for(int i=0;i<row.size();i++){
		//    System.out.println(row.get(i));
		//   }
		//AccessDBH.updateClob();
		System.out.println(AccessDBConnection.getConnection());
		try {
			 AccessDBConnection db=new AccessDBConnection();
//			 Map map=new HashMap();
//			 for(int i=0;i<32;i++){
//				 map.put("a"+i, i);
//			 }
//			 String p_device_status_id=db.getParentStatusId("192.168.1.130");
//			 db.saveDeviceStatusByParentKeyAndMap(p_device_status_id, map);
			 //System.out.println(db.getDateValue());
			 
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public boolean saveDeviceStatusByParentKeyAndMap(String parent_key,Map statusMap) 
	              throws Exception{
		boolean is_success=false;
		if(statusMap==null||statusMap.size()==0){
			return false;
		}
		String table_name="atmp_device_status_original";
		Connection conn=AccessDBConnection.getConnection();
		String sql="insert into "+table_name+"  "+
				   "(device_status_original_id,p_device_status_original_id, "+
					"device_column_key,device_column_value,time_mark ) "+
					"values(?,?,?,?,"+getDateValue()+")";
		PreparedStatement pst=conn.prepareStatement(sql);
		conn.setAutoCommit(false);
		Iterator it=statusMap.entrySet().iterator();
		while(it.hasNext()){
			Entry e=(Entry) it.next();
			String key=(String) e.getKey();
			String value=(String) e.getValue().toString();
			System.out.println("key="+key+",value="+value);
			pst.setString(1, PrimaryKeyUtil.getPrimaryKey(table_name, null));
			pst.setString(2, parent_key);
			pst.setString(3, key);
			pst.setString(4, value);
			pst.addBatch();
		}
		pst.executeBatch();
		conn.commit();
		pst.clearBatch();
		return is_success;
	}
	
	public String getParentStatusId(String deviceIp) throws Exception{
		Connection conn=AccessDBConnection.getConnection();
		Date d=new Date();
		String table_name="atmp_device_status_original";
		String sql="insert into "+table_name+"  "+
				   "(device_status_original_id,p_device_status_original_id, "+
					"device_column_key,device_column_value,time_mark ) "+
					"values(?,?,?,?,"+getDateValue()+")";
		PreparedStatement pst=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
		String primary_key=PrimaryKeyUtil.getPrimaryKey(table_name,null);
		if(primary_key.equals("")){
			logger.error(table_name+"：getParentStatusId:获取主键异常！");
			return "";
		}
		pst.setString(1, primary_key);
		pst.setString(2, null);
		pst.setString(3, "DeviceIp");
		pst.setString(4, deviceIp);
		pst.execute();
		sql="select *from atmp_device_status_original adso where adso.device_column_key='DeviceIp'" +
				" and adso.device_column_value='"+deviceIp+"'"+
				" and adso.p_device_status_original_id is null";
		PreparedStatement pst2=conn.prepareStatement(sql);
		ResultSet rs=pst2.executeQuery();
		String device_status_original_id="";
		while(rs.next()){
			 device_status_original_id=rs.getString("device_status_original_id");
		}
		System.out.println("device_status_original_id="+device_status_original_id);
		return device_status_original_id;
	}
	
	public static String getDateValue() throws Exception{
		Date d=new Date();
		return "to_date('"+d.toLocaleString()+"','yyyy-MM-dd hh24:mi:ss')";
	}
	/**
	 * @author zengqun
	 * @remark 检查设备当前状态表：ip惟一
	 * @param deviceIp
	 * @return
	 * @throws Exception
	 */
	public boolean checkDeviceStatusCurrentDeviceIpUnique(String deviceIp) throws Exception{
//		Connection conn=AccessDBConnection.getConnection();
//		Date d=new Date();
//		String table_name="atmp_device_status_original";
//		String sql="insert into "+table_name+"  "+
//				   "(device_status_original_id,p_device_status_original_id, "+
//					"device_column_key,device_column_value,time_mark ) "+
//					"values(?,?,?,?,to_date('"+d.toLocaleString()+"','yyyy-MM-dd hh24:mi:ss'))";
//		PreparedStatement pst=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
//		String primary_key=PrimaryKeyUtil.getPrimaryKey(table_name,null);
//		if(primary_key.equals("")){
//			logger.error(table_name+"：getParentStatusId:获取主键异常！");
//			return false;
//		}
//		pst.setString(1, primary_key);
//		pst.setString(2, null);
//		pst.setString(3, "DeviceIp");
//		pst.setString(4, deviceIp);
//		pst.execute();
//		sql="select *from atmp_device_status_original adso where adso.device_column_key='DeviceIp'" +
//				" and adso.device_column_value='"+deviceIp+"'"+
//				" and adso.p_device_status_original_id is null";
//		PreparedStatement pst2=conn.prepareStatement(sql);
//		ResultSet rs=pst2.executeQuery();
//		String device_status_original_id="";
//		while(rs.next()){
//			 device_status_original_id=rs.getString("device_status_original_id");
//		}
//		System.out.println("device_status_original_id="+device_status_original_id);
		return true;
	}

}
